using System;
using System.Data.SQLite;
using System.Collections.Generic;
using test8_h1.Models;

namespace test8_h1.Data
{
    public class DataAccess
    {
        private static string connectionString = $"Data Source=StudentManagement.db;Version=3;";

        #region School Operations
        public static int AddSchool(School school)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "INSERT INTO School (Name, Address) VALUES (@Name, @Address); SELECT last_insert_rowid();";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Name", school.Name);
                    command.Parameters.AddWithValue("@Address", school.Address);
                    int id = Convert.ToInt32(command.ExecuteScalar());
                    DatabaseHelper.LogOperation("Add School", $"Added school: {school.Name}");
                    return id;
                }
            }
        }

        public static void UpdateSchool(School school)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "UPDATE School SET Name = @Name, Address = @Address WHERE Id = @Id";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Id", school.Id);
                    command.Parameters.AddWithValue("@Name", school.Name);
                    command.Parameters.AddWithValue("@Address", school.Address);
                    command.ExecuteNonQuery();
                    DatabaseHelper.LogOperation("Update School", $"Updated school: {school.Name}");
                }
            }
        }

        public static void DeleteSchool(int id)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "DELETE FROM School WHERE Id = @Id";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    command.ExecuteNonQuery();
                    DatabaseHelper.LogOperation("Delete School", $"Deleted school with ID: {id}");
                }
            }
        }

        public static List<School> GetAllSchools()
        {
            var schools = new List<School>();
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT * FROM School";
                using (var command = new SQLiteCommand(sql, connection))
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        schools.Add(new School
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Address = reader.GetString(2)
                        });
                    }
                }
            }
            return schools;
        }
        #endregion

        #region Class Operations
        public static int AddClass(Class cls)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "INSERT INTO Class (Name, Grade, SchoolId) VALUES (@Name, @Grade, @SchoolId); SELECT last_insert_rowid();";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Name", cls.Name);
                    command.Parameters.AddWithValue("@Grade", cls.Grade);
                    command.Parameters.AddWithValue("@SchoolId", cls.SchoolId);
                    int id = Convert.ToInt32(command.ExecuteScalar());
                    DatabaseHelper.LogOperation("Add Class", $"Added class: {cls.Name}");
                    return id;
                }
            }
        }

        public static void UpdateClass(Class cls)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "UPDATE Class SET Name = @Name, Grade = @Grade, SchoolId = @SchoolId WHERE Id = @Id";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Id", cls.Id);
                    command.Parameters.AddWithValue("@Name", cls.Name);
                    command.Parameters.AddWithValue("@Grade", cls.Grade);
                    command.Parameters.AddWithValue("@SchoolId", cls.SchoolId);
                    command.ExecuteNonQuery();
                    DatabaseHelper.LogOperation("Update Class", $"Updated class: {cls.Name}");
                }
            }
        }

        public static void DeleteClass(int id)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "DELETE FROM Class WHERE Id = @Id";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    command.ExecuteNonQuery();
                    DatabaseHelper.LogOperation("Delete Class", $"Deleted class with ID: {id}");
                }
            }
        }

        public static List<Class> GetClassesBySchool(int schoolId)
        {
            var classes = new List<Class>();
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT * FROM Class WHERE SchoolId = @SchoolId";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@SchoolId", schoolId);
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            classes.Add(new Class
                            {
                                Id = reader.GetInt32(0),
                                Name = reader.GetString(1),
                                Grade = reader.GetInt32(2),
                                SchoolId = reader.GetInt32(3)
                            });
                        }
                    }
                }
            }
            return classes;
        }
        #endregion

        #region Student Operations
        public static int AddStudent(Student student)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "INSERT INTO Student (Name, Age, Gender, ClassId) VALUES (@Name, @Age, @Gender, @ClassId); SELECT last_insert_rowid();";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Name", student.Name);
                    command.Parameters.AddWithValue("@Age", student.Age);
                    command.Parameters.AddWithValue("@Gender", student.Gender);
                    command.Parameters.AddWithValue("@ClassId", student.ClassId);
                    int id = Convert.ToInt32(command.ExecuteScalar());
                    DatabaseHelper.LogOperation("Add Student", $"Added student: {student.Name}");
                    return id;
                }
            }
        }

        public static void UpdateStudent(Student student)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "UPDATE Student SET Name = @Name, Age = @Age, Gender = @Gender, ClassId = @ClassId WHERE Id = @Id";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Id", student.Id);
                    command.Parameters.AddWithValue("@Name", student.Name);
                    command.Parameters.AddWithValue("@Age", student.Age);
                    command.Parameters.AddWithValue("@Gender", student.Gender);
                    command.Parameters.AddWithValue("@ClassId", student.ClassId);
                    command.ExecuteNonQuery();
                    DatabaseHelper.LogOperation("Update Student", $"Updated student: {student.Name}");
                }
            }
        }

        public static void DeleteStudent(int id)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "DELETE FROM Student WHERE Id = @Id";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    command.ExecuteNonQuery();
                    DatabaseHelper.LogOperation("Delete Student", $"Deleted student with ID: {id}");
                }
            }
        }

        public static List<Student> GetStudentsByClass(int classId)
        {
            var students = new List<Student>();
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT * FROM Student WHERE ClassId = @ClassId";
                using (var command = new SQLiteCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@ClassId", classId);
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            students.Add(new Student
                            {
                                Id = reader.GetInt32(0),
                                Name = reader.GetString(1),
                                Age = reader.GetInt32(2),
                                Gender = reader.GetString(3),
                                ClassId = reader.GetInt32(4)
                            });
                        }
                    }
                }
            }
            return students;
        }
        public static List<Student> GetAllStudents()
        {
            var students = new List<Student>();
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT * FROM Student";
                using (var command = new SQLiteCommand(sql, connection))
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        students.Add(new Student
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Age = reader.GetInt32(2),
                            Gender = reader.GetString(3),
                            ClassId = reader.GetInt32(4)
                        });
                    }
                }
            }
            return students;
        }
        #endregion

        #region Log Operations
        public static List<Log> GetAllLogs()
        {
            var logs = new List<Log>();
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT * FROM Log ORDER BY Timestamp DESC";
                using (var command = new SQLiteCommand(sql, connection))
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        logs.Add(new Log
                        {
                            Id = reader.GetInt32(0),
                            Operation = reader.GetString(1),
                            Details = reader.GetString(2),
                            Timestamp = reader.GetDateTime(3)
                        });
                    }
                }
            }
            return logs;
        }
        #endregion
    }
}